package CCRMServer.Controllers;
/** 
 * this class is for managing sales.
 * all methods are static because we don't need to create an object for using those methods.
 * If there are more things to add please tell me and I will add.
 * 2 of the managers from our class diagram are united with the sale manager.
 * @author Oz Mouchtar
 *
 */
import CCRMServer.DBConnect;
import Common.*;
import Common.Entities.*;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.TimeUnit;
public class ServerSaleMangement {
	/*For now the methods are empty*/
	
	/**
	 * This method registers a new sale into the DB.
	 * @param sale
	 * @return
	 */
	public static ServerAnswer addSale(Sale sale) {
		String table;
		String fields;
		String filter;
		String[] insFields = new String[5];
		String[] insValues = new String[5];
		ArrayList<String> saleMen = new ArrayList<String>();
		ArrayList<String> customers = new ArrayList<String>();
		Connection conDB  = DBConnect.mySQLConnection();
		int saleID;
		boolean isOK;
		try {
			// add new sale to the DB
			table = "sales";
			insFields[0] = "saleName";
			insFields[1] = "saleDescription";
			insFields[2] = "saleStartDate";
			insFields[3] = "saleFinishDate";
			insFields[4] = "templateID";
			
			insValues[0] = sale.getSaleName();
			insValues[1] = sale.getSaleDescription();
			insValues[2] = sale.getSaleStrartString();
			insValues[3] = sale.getSaleFinishString();
			insValues[4] = Integer.toString(sale.getTemplateID());
			
			isOK = DBConnect.insertSingleRecord(conDB, table, insFields, insValues);
			if (!isOK) throw new SQLException();
			
			// get new saleID
			fields = "MAX(saleID)";
			ResultSet rs = DBConnect.selectWithFilter(conDB, table, fields, null);
			if (rs.next())
				saleID = rs.getInt(1);
			else throw new SQLException();
			rs.close();
						
			// get list of sale-men
			table = "employees";
			fields = "employeeID, employeeName";
			filter = "employeePosition = 'Sales Man'";
			rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
		
			while (rs.next()) {
				
		
				saleMen.add(rs.getString(1));
			}
			rs.close();
			if (saleMen.isEmpty())
				return new ServerAnswer(SERVER_COMMANDS.ADD_SALE, null, "No available sale men");
			
			// get list of customers to assign
			table = "customersintemplates";
			fields = "customerID";
			filter = "templateID = '" + Integer.toString(sale.getTemplateID()) + "'";
			rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
	
			while (rs.next()) {

				customers.add(rs.getString(1));
			}
			
			// assign customers
			table = "saleteamcustomers";
			insFields = new String[3];
			insValues = new String[3];
			
			insFields[0] = "employeeID";
			insFields[1] = "SaleID";
			insFields[2] = "customerID";
			insValues[1] = Integer.toString(saleID);
			
			int dose = customers.size() / saleMen.size();
			int i = 0;
			if (dose == 0) {
				for (String customerID : customers) {
					insValues[0] = saleMen.get(i);
					insValues[2] = customerID;
					DBConnect.insertSingleRecord(conDB, table, insFields, insValues);
					i ++;
				}
				return new ServerAnswer(SERVER_COMMANDS.ADD_SALE, null, "Action Succeeded");
			}
			for (String employeeID : saleMen) {
				for (int j = 0 ; j < dose ; j ++) {
					insValues[0] = employeeID;
					insValues[2] = customers.get(i+j);
					DBConnect.insertSingleRecord(conDB, table, insFields, insValues);
				}
				i = i + dose;
			}
			i = 0;
			for (int k = (dose * saleMen.size()) ; k < customers.size() ; k ++) {
				insValues[0] = saleMen.get(i);
				insValues[2] = customers.get(k);
				DBConnect.insertSingleRecord(conDB, table, insFields, insValues);
				i ++;
			}
			return new ServerAnswer(SERVER_COMMANDS.ADD_SALE, null, "Action Succeeded");
		} catch (SQLException e){
		 return new ServerAnswer(SERVER_COMMANDS.ADD_SALE, null, "DB Problem");
		}
	}
	
	/**
	 * this method is for sale details.
	 * @param saleID
	 * @return
	 */
	public static ServerAnswer getSaleDetails (int saleID){
		String table = "sales";
		String filter = "saleID = '" + Integer.toString(saleID) + "'";
		Sale sale = null;
		Connection conDB = DBConnect.mySQLConnection();
		ResultSet res = null;
		try {
			res = DBConnect.selectWithFilter(conDB, table, null, filter);
			if (res.next())
				sale = new Sale(res.getInt(1), res.getString(2), res.getString(3), res.getDate(4), res.getDate(5), res.getInt(6));
			else
				return new ServerAnswer(SERVER_COMMANDS.SHOW_SALE_DETAILS, null, "Sale does not exists");
			return new ServerAnswer(SERVER_COMMANDS.SHOW_SALE_DETAILS,sale, "");
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.SHOW_SALE_DETAILS, null, "DB Problem");
		}

	 }
	

	/**
	 * This method is getting Sales names and numbers for salesman id. 
	 * @param salesmanID
	 * @return ServerAnswer with ArrayList of String with names and ID's
	 */
	public static ServerAnswer showSalesForSalesman(String salesmanID){
		String table = "Sales S, SaleTeamCustomers STC";
		String fields = " DISTINCT S.saleID, S.saleName";
		String filter = "STC.employeeID = '" + salesmanID + "' AND S.saleID = STC.saleID";
		Connection conDB = DBConnect.mySQLConnection();
		ResultSet res = null;
		
		ArrayList<String[]> salesList = new ArrayList<String[]>();
		String[] row = new String[2];
		
		try {
			res = DBConnect.selectWithFilter(conDB, table, fields, filter);
			while(res.next()) {
				row[0] = res.getString(1);
				row[1] = res.getString(2);
				salesList.add(row.clone());
			}
			return new ServerAnswer(SERVER_COMMANDS.VIEW_SALES_FOR_SALESMAN, salesList, "");	
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.VIEW_SALES_FOR_SALESMAN, null, "DB Problem");
		}
	}

	/**
	 * This method check if a customer was addressed in the last week or in the current sale
	 * @param aData - String array with the following format: {saleID, customerID}
	 * @return
	 */
	public static ServerAnswer addressCustomer(String[] aData) {
		String table = "customersresponses";
		String fields = "responseDate";
		String filter;
		Connection conDB = DBConnect.mySQLConnection();
		ResultSet rs;
		
		try {
			// check if customer was addressed in the last week
			filter = "responseID = (SELECT MAX(responseID) FROM customersresponses WHERE customerID = '" + aData[1] + "')";
			rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
			if (rs.next()) {
				Date today = new Date();
				Date lastCall = rs.getDate(1);
				long dif = getDateDiff(lastCall, today, TimeUnit.DAYS);
				if (dif <= 7)
					return new ServerAnswer(SERVER_COMMANDS.ADDRESS_CUSTOMER, null, "This customer was addressed in the last week");
			}
			rs.close();
			
			// check if the customer was addressed in this sale
			filter = "saleID = '" + aData[0] + "' AND customerID = '" + aData[1] + "'";
			rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
			if (rs.next())
				return new ServerAnswer(SERVER_COMMANDS.ADDRESS_CUSTOMER, null, "This customer was addressed in this sale");
			return new ServerAnswer(SERVER_COMMANDS.ADDRESS_CUSTOMER, aData, "");
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ADDRESS_CUSTOMER, null, "DB Problem");
		}
	}

	/**
	 * This method return the difference between 2 dates in any time unit you might need.
	 * @param date1 - the oldest date
	 * @param date2 - the newest date
	 * @param timeUnit - desired time unit
	 * @return - long with the dates difference
	 */
	private static long getDateDiff(Date date1, Date date2, TimeUnit timeUnit) {
	    long diffInMillies = date2.getTime() - date1.getTime();
	    return timeUnit.convert(diffInMillies,TimeUnit.MILLISECONDS);
	}




}

